// dao/ttsHistory.js - Updated with support for voice instructions

import { connect } from '../db'
import { ensureConfigInitialized, serviceUrl } from '../config/config'
import log from '../logger'
import fs from 'fs'
import path from 'path'
import dayjs from 'dayjs'
import archiver from 'archiver'

// 初始化数据库表结构（确保添加新的instructions列）
export function initTtsHistoryTable() {
  const db = connect();

  // 检查表是否存在
  const tableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='tts_history'").get();

  if (!tableExists) {
    // 创建包含instructions列的新表
    db.prepare(`
      CREATE TABLE tts_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        history_id INTEGER,
        name TEXT,
        text TEXT,
        audio_path TEXT,
        duration INTEGER,
        model TEXT,
        voice TEXT,
        language TEXT,
        instructions TEXT,
        caption_path TEXT,
        caption_status TEXT DEFAULT 'none',
        caption_error TEXT,
        caption_format TEXT DEFAULT 'srt',
        created_at INTEGER
      )
    `).run();
  } else {
    // 检查是否需要添加instructions列
    try {
      db.prepare("SELECT instructions FROM tts_history LIMIT 1").get();
    } catch (error) {
      // 如果列不存在，添加它
      if (error.message.includes('no such column')) {
        db.prepare("ALTER TABLE tts_history ADD COLUMN instructions TEXT").run();
      } else {
        throw error;
      }
    }
  }
}

// 获取所有TTS历史记录
export function getAllTtsHistory() {
  const db = connect()
  return db.prepare('SELECT * FROM tts_history ORDER BY created_at DESC').all()
}

// 添加新TTS历史记录，包含指令参数
export function addTtsHistory(data) {
  const db = connect()
  const now = Date.now()

  const {
    name, text, originalName, originalText, audioPath, duration,
    model, voice, language = null, instructions = null,
    captionPath = null, captionStatus = 'none', captionFormat = 'srt'
  } = data;

  const info = db.prepare(
    `INSERT INTO tts_history (
      name, text, name_origin, text_origin, audio_path, duration,
      model, voice, language, instructions,
      caption_path, caption_status, caption_format, created_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
  ).run(
    name, text, originalName, originalText, audioPath, duration,
    model, voice, language, instructions,
    captionPath, captionStatus, captionFormat, now
  );

  return { ...info, id: info.lastInsertRowid };
}

// 更新字幕信息
export function updateTtsHistoryCaption(id, captionPath, captionStatus, captionError = null) {
  const db = connect()
  const info = db.prepare(
    'UPDATE tts_history SET caption_path = ?, caption_status = ?, caption_error = ? WHERE id = ?'
  ).run(captionPath, captionStatus, captionError, id)
  return info
}

// 更新Id
export function updateTtsHistoryId(id, historyId) {
  const db = connect()
  const info = db.prepare(
    'UPDATE tts_history SET history_id = ? WHERE id = ?'
  ).run(historyId, id)
  return info
}

// 根据ID获取TTS历史记录
export function getTtsHistoryById(id) {
  const db = connect()
  return db.prepare('SELECT * FROM tts_history WHERE id = ?').get(id)
}

// 批量获取TTS历史记录
export function getTtsHistoryByIds(ids) {
  const db = connect()
  const placeholders = ids.map(() => '?').join(',')
  return db.prepare(`SELECT * FROM tts_history WHERE id IN (${placeholders})`).all(...ids)
}

// 删除TTS历史记录
export function deleteTtsHistory(id) {
  const db = connect()

  // 获取记录以便删除文件
  const record = getTtsHistoryById(id)

  if (record) {
    // 删除音频文件
    if (record.audio_path && fs.existsSync(record.audio_path)) {
      try {
        fs.unlinkSync(record.audio_path)
      } catch (err) {
        log.error(`Failed to delete audio file: ${record.audio_path}`, err)
      }
    }

    // 删除字幕文件
    if (record.caption_path && fs.existsSync(record.caption_path)) {
      try {
        fs.unlinkSync(record.caption_path)
      } catch (err) {
        log.error(`Failed to delete caption file: ${record.caption_path}`, err)
      }
    }
  }

  const info = db.prepare('DELETE FROM tts_history WHERE id = ?').run(id)
  return info
}

// 搜索TTS历史记录
export function searchTtsHistory(keyword) {
  const db = connect()
  return db.prepare(
    `SELECT * FROM tts_history
     WHERE name LIKE ? OR text LIKE ? OR instructions LIKE ?
     ORDER BY created_at DESC`
  ).all(`%${keyword}%`, `%${keyword}%`, `%${keyword}%`)
}

// 删除所有TTS历史记录
export function deleteAllTtsHistory() {
  const db = connect()

  // 获取所有记录以便删除文件
  const records = getAllTtsHistory()

  // 删除所有相关文件
  records.forEach(record => {
    if (record.audio_path && fs.existsSync(record.audio_path)) {
      try {
        fs.unlinkSync(record.audio_path)
      } catch (err) {
        log.error(`Failed to delete audio file: ${record.audio_path}`, err)
      }
    }

    if (record.caption_path && fs.existsSync(record.caption_path)) {
      try {
        fs.unlinkSync(record.caption_path)
      } catch (err) {
        log.error(`Failed to delete caption file: ${record.caption_path}`, err)
      }
    }
  })

  const info = db.prepare('DELETE FROM tts_history').run()
  return info
}

// 下载单个音频文件
export function downloadAudioFile(id) {
  const record = getTtsHistoryById(id)

  if (!record || !record.audio_path) {
    throw new Error('音频文件不存在')
  }

  if (!fs.existsSync(record.audio_path)) {
    throw new Error('音频文件未找到')
  }

  return {
    filePath: record.audio_path,
    fileName: path.basename(record.audio_path),
    name: record.name
  }
}

// 下载单个字幕文件
export function downloadCaptionFile(id) {
  const record = getTtsHistoryById(id)

  if (!record || !record.caption_path) {
    throw new Error('字幕文件不存在')
  }

  if (!fs.existsSync(record.caption_path)) {
    throw new Error('字幕文件未找到')
  }

  return {
    filePath: record.caption_path,
    fileName: path.basename(record.caption_path),
    name: record.name
  }
}

// 批量下载文件（音频和字幕）
export async function batchDownloadFiles(ids, includeAudio = true, includeCaption = true) {
  console.log(ids, includeAudio, includeCaption)
  const records = getTtsHistoryByIds(ids)

  if (records.length === 0) {
    throw new Error('未找到相关记录')
  }

  // 创建临时目录
  const tempDir = path.join(process.cwd(), 'temp', `batch_${Date.now()}`)
  if (!fs.existsSync(tempDir)) {
    fs.mkdirSync(tempDir, { recursive: true })
  }

  // 创建zip文件路径
  const zipFileName = `tts_batch_${dayjs().format('YYYYMMDD_HHmmss')}.zip`
  const zipFilePath = path.join(tempDir, zipFileName)

  // 创建zip流
  const output = fs.createWriteStream(zipFilePath)
  const archive = archiver('zip', {
    zlib: { level: 9 } // 设置压缩级别
  })

  return new Promise((resolve, reject) => {
    output.on('close', () => {
      resolve({
        filePath: zipFilePath,
        fileName: zipFileName,
        size: archive.pointer()
      })
    })

    archive.on('error', (err) => {
      reject(err)
    })

    archive.pipe(output)

    // 添加文件到zip
    records.forEach((record, index) => {
      const filePrefix = `${index + 1}_${record.name || 'unnamed'}`

      // 添加音频文件
      if (includeAudio && record.audio_path && fs.existsSync(record.audio_path)) {
        const audioExt = path.extname(record.audio_path)
        archive.file(record.audio_path, { name: `${filePrefix}${audioExt}` })
      }

      // 添加字幕文件
      if (includeCaption && record.caption_path && fs.existsSync(record.caption_path)) {
        const captionExt = path.extname(record.caption_path)
        archive.file(record.caption_path, { name: `${filePrefix}${captionExt}` })
      }
    })

    archive.finalize()
  })
}

// 获取字幕内容
export function getCaptionContent(id) {
  const record = getTtsHistoryById(id)
  console.log(record)

  if (!record || !record.caption_path) {
    throw new Error('字幕文件不存在')
  }

  if (!fs.existsSync(record.caption_path)) {
    throw new Error('字幕文件未找到')
  }

  const content = fs.readFileSync(record.caption_path, 'utf8')

  return {
    content,
    format: record.caption_format,
    name: record.name,
    caption_path: record.caption_path
  }
}

// 获取字幕内容
export function getCaptionContentWithPath(caption_path) {

  if (!caption_path) {
    throw new Error('字幕文件不存在')
  }

  if (!fs.existsSync(caption_path)) {
    throw new Error('字幕文件未找到')
  }

  const content = fs.readFileSync(caption_path, 'utf8')

  return {
    content,
    format: 'srt',
    caption_path: caption_path
  }
}

/**
 * 发送音频项目到Kpop服务并上传相关文件（音频和字幕一起上传）
 * @param {Array} audioItems 要处理的音频项目数组
 * @param uuidv4 唯一id
 * @returns {Promise<Object>} 包含成功状态和详细信息的结果对象
 */
export async function sendToKpop(audioItems, uuidv4) {
  // 确保配置初始化完成
  await ensureConfigInitialized();

  // 从配置中获取基础URL
  const kpopBaseUrl = serviceUrl.kpopBaseUrl;

  /**
   * 上传音频和字幕文件（一次性上传）
   * @param {Object} item 音频信息
   * @param {number} index 序号
   * @returns {Promise<Object>} 上传结果
   */
  async function uploadAudioAndSubtitle(item, index) {
    const fs = require('fs');
    const path = require('path');
    const FormData = require('form-data');
    const axios = require('axios');

    // 检查音频文件是否存在
    if (!fs.existsSync(item.audioUrl)) {
      throw new Error(`音频文件不存在: ${item.audioUrl}`);
    }

    // 创建FormData
    const formData = new FormData();

    // 添加音频文件
    const audioFileName = path.basename(item.audioUrl);
    formData.append('audio_file', fs.createReadStream(item.audioUrl), {
      filename: audioFileName,
      contentType: 'audio/wav'
    });

    // 如果有字幕文件，添加字幕
    let hasSubtitle = false;
    if (item.captionPath && fs.existsSync(item.captionPath)) {
      const subtitleFileName = path.basename(item.captionPath);
      formData.append('subtitle_file', fs.createReadStream(item.captionPath), {
        filename: subtitleFileName,
        contentType: 'text/plain'
      });
      hasSubtitle = true;
      log.debug(`包含字幕文件: ${subtitleFileName}`);
    } else if (item.captionPath) {
      log.warn(`字幕文件不存在: ${item.captionPath}`);
    }

    // 构建元数据
    const metaData = {
      id: item.id,
      name: item.name || item.originalName,
      text: item.text || item.originalText,
      originalName: item.originalName,
      originalText: item.originalText,
      language: item.language || 'zh',
      duration: item.duration,
      hasSubtitle: hasSubtitle,
      order: index
    };

    console.log("formData"+JSON.stringify(formData));

    // 添加其他表单字段
    formData.append('group_id', uuidv4);
    formData.append('meta_data', JSON.stringify(metaData));
    formData.append('order', index.toString());

    try {
      // 使用新地合并上传接口
      const response = await axios.post(`${kpopBaseUrl}:5000/api/merge/audio`, formData, {
        headers: {
          ...formData.getHeaders(),
          'accept': 'application/json'
        },
        timeout: 120000, // 增加超时时间到2分钟
        maxContentLength: Infinity,
        maxBodyLength: Infinity
      });

      if (response.data && response.data.success) {
        log.debug(`文件上传成功: ${audioFileName} (字幕: ${hasSubtitle ? '有' : '无'})`);
        return {
          success: true,
          audioFileName: audioFileName,
          hasSubtitle: hasSubtitle,
          response: response.data
        };
      } else {
        throw new Error(`上传失败: ${response.data?.message || '未知错误'}`);
      }
    } catch (error) {
      if (error.response) {
        throw new Error(`上传失败，状态码: ${error.response.status}, 错误: ${error.response.data?.message || error.response.statusText}`);
      } else {
        throw new Error(`上传失败: ${error.message}`);
      }
    }
  }

  try {
    log.debug('开始处理音频项目，数量:', audioItems.length);
    log.debug('Group ID:', uuidv4);

    // 为每个项目确保有唯一ID
    audioItems.forEach(item => {
      if (!item.id) {
        item.id = require('uuid').v4();
      }
    });

    // 串行上传每个项目，确保顺序
    const uploadResults = [];

    for (let i = 0; i < audioItems.length; i++) {
      const item = audioItems[i];
      const index = i + 1; // 序号从1开始

      log.debug(`上传第 ${index}/${audioItems.length} 个项目: ${item.name || item.originalName}`);

      try {
        const result = await uploadAudioAndSubtitle(item, index);

        uploadResults.push({
          success: true,
          itemId: item.id,
          itemName: item.name || item.originalName,
          index: index,
          audioFileName: result.audioFileName,
          hasSubtitle: result.hasSubtitle,
          serverResponse: result.response
        });

        log.info(`✓ 上传成功 [${index}/${audioItems.length}]: ${item.name || item.originalName}`);
      } catch (error) {
        log.error(`✗ 上传失败 [${index}/${audioItems.length}] ${item.name || item.originalName}: ${error.message}`);

        uploadResults.push({
          success: false,
          itemId: item.id,
          itemName: item.name || item.originalName,
          index: index,
          error: error.message
        });
      }

      // 添加短暂延迟，避免服务器压力过大
      if (i < audioItems.length - 1) {
        await new Promise(resolve => setTimeout(resolve, 500));
      }
    }

    // 统计结果
    const successCount = uploadResults.filter(r => r.success).length;
    const failedCount = uploadResults.filter(r => !r.success).length;
    const withSubtitleCount = uploadResults.filter(r => r.success && r.hasSubtitle).length;

    log.info(`
    ========== 上传完成统计 ==========
    总数: ${audioItems.length}
    成功: ${successCount}
    失败: ${failedCount}
    包含字幕: ${withSubtitleCount}
    ================================
    `);

    // 如果有失败的项目，打印详细信息
    if (failedCount > 0) {
      log.error('失败项目详情:');
      uploadResults.filter(r => !r.success).forEach(r => {
        log.error(`  - [${r.index}] ${r.itemName}: ${r.error}`);
      });
    }

    return {
      success: failedCount === 0,
      uploadResults: uploadResults,
      summary: {
        total: audioItems.length,
        success: successCount,
        failed: failedCount,
        withSubtitle: withSubtitleCount
      },
      groupId: uuidv4
    };
  } catch (err) {
    log.error(`Kpop处理失败: ${err.message}`, err);

    return {
      success: false,
      error: err.message,
      errorDetails: err.stack
    };
  }
}
